﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections.Generic;

namespace VIT.Provider
{
    public class Function : VIT.DataHelper.DataAccess.SQLSupport
    {
        #region Variable
        string user = HttpContext.Current.Session["userName"].ToString();
        #endregion

        #region Properties
        public string Name { get; set; }
        public string Page { get; set; }
        public string Description { get; set; }
        #endregion

        #region Constructor
        public Function()    
        {            
            this.Name = string.Empty;
            this.Page = string.Empty;
            this.Description = string.Empty;
        }
        #endregion

        #region Methods
        public DataTable getAll(string SortColumn = "Name")
        {
            if (SortColumn == "") SortColumn = "Name";
            string sqlCat = "select * from aFunction order by " + SortColumn;
            return ExecuteDataset(false, sqlCat).Tables[0];
        }

        public bool Delete(string name)
        {
            string strQuery = "Delete from aFunction where Name=@Name";
            SqlParameter Name = new SqlParameter("@Name", name);

            return ExecuteNonQuery(false, strQuery, Name) > 0;
        }

        public void GetSingle()
        {
            if (Exist())
            {
                string strQuery = "select * from aFunction where Name=@Name";
                SqlParameter Name = new SqlParameter("@Name", this.Name);
                DataTable dt = ExecuteDataset(false, strQuery, Name).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    this.Page = dt.Rows[0]["Page"].ToString();
                    this.Name = dt.Rows[0]["Name"].ToString();
                    this.Description = dt.Rows[0]["Description"].ToString();
                }
            }
            else this.Name = "";
        }

        public List<string> getDistinctPage()
        {
            string sqlCat = "select distinct Page from aFunction order by Page";
            DataTable table = ExecuteDataset(false, sqlCat).Tables[0];
            List<string> list = new List<string>();
            foreach (DataRow row in table.Rows)
            {
                list.Add(row[0].ToString());
            }
            return list;
        }

        public DataTable getByPage(string page, string SortColumn = "Name")
        {
            string sqlCat = "select * from aFunction where Page=@Page order by " + SortColumn;
            SqlParameter Page = new SqlParameter("@Page", page);
            return ExecuteDataset(false, sqlCat, Page).Tables[0];
        }

        public bool Exist()
        {
            if (this.Name == "") return false;
            string strQuery = "select Name from aFunction where Name=@Name";
            SqlParameter Name = new SqlParameter("@Name", this.Name);
            return ExecuteScalar(false, strQuery, Name) != null;
        }

        public bool Save()
        {
            if (Exist())
            {
                return Update();
            }
            else
            {
                return Insert();
            }
        }

        public bool Insert()
        {
            string strQuery = "insert into aFunction values (@Name, @Page, @Description, @Now, @Now, @User, @User)";

            SqlParameter Page = new SqlParameter("@Page", this.Page);
            SqlParameter Name = new SqlParameter("@Name", this.Name);
            SqlParameter Description = new SqlParameter("@Description", this.Description);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, strQuery, Page, Name, Description, User, Now) > 0;            
        }

        public bool Update()
        {
            string strQuery = "update aFunction set Page=@Page, Description=@Description, ModifyDate=@Now, ModifyByUser=@User where Name=@Name";

            SqlParameter Page = new SqlParameter("@Page", this.Page);
            SqlParameter Name = new SqlParameter("@Name", this.Name);
            SqlParameter Description = new SqlParameter("@Description", this.Description);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, strQuery, Page, Name, Description, User, Now) > 0;            
        }
        #endregion

    }
}
